package DAO;

import Connect.ConnectDB;
import java.awt.List;
import javax.swing.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class VacDAO {

    Connection conn;
    ResultSet rsVac = null;
    Statement st;
    PreparedStatement pst;

    public VacDAO() {
        ConnectDB cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataVac();
    }

    public Vector loadVacToUpdate(String VacId) {
        Vector vecVacData = new Vector();
        try {
            rsVac.beforeFirst();

            while (rsVac.next()) {
                if (VacId.equals(rsVac.getString("VACANCYID"))) {

                    vecVacData.add(rsVac.getObject(1));
                    vecVacData.add(rsVac.getObject(2));
                    vecVacData.add(rsVac.getObject(3));
                    vecVacData.add(rsVac.getObject(4));
                    vecVacData.add(rsVac.getObject(5));
                    vecVacData.add(rsVac.getObject(6));
                    vecVacData.add(rsVac.getObject(7));
                    vecVacData.add(rsVac.getObject(8));
                }
            }

            return vecVacData;
        } catch (Exception e) {
            System.out.println("Error in loadVacToUpdateForm: " + e.getMessage());
            return null;
        }
    }

    public void loadDataVac() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Vacancies";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsVac = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String checkVacId(String EmpId) {
        try {
            loadDataVac();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").equals(EmpId)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            System.out.println("Error in checkEmpId: " + e.getMessage());
            return "Error";
        }
    }

    public Vector vacLoadDepNo() {

        ResultSet rs = null;
        try {
            String query = "SELECT DEPTNO FROM Departments";
            PreparedStatement ps = conn.prepareStatement(query);
            rs = ps.executeQuery();
            Vector vecDepNo = new Vector();
            while (rs.next()) {
                vecDepNo.add(rs.getString(1));
            }
            return vecDepNo;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }

    }
    
    public Vector vacLoadDepName() {

        ResultSet rs = null;
        try {
            String query = "SELECT DEPTNAME FROM Departments";
            PreparedStatement ps = conn.prepareStatement(query);
            rs = ps.executeQuery();
            Vector vecDepName = new Vector();
            while (rs.next()) {
                vecDepName.add(rs.getString(1));
            }
            return vecDepName;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }

    }
    

    public Vector vacLoadSecId() {
        ResultSet rs = null;
        try {
            String query = "SELECT SECTID FROM Sections";
            PreparedStatement ps = conn.prepareStatement(query);
            rs = ps.executeQuery();
            Vector vec = new Vector();
            while (rs.next()) {
                vec.add(rs.getString(1));
            }
            return vec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public Vector vacLoadDesId() {
        ResultSet rs = null;
        try {
            String query = "SELECT DESIGNID FROM Designation";
            PreparedStatement ps = conn.prepareStatement(query);
            rs = ps.executeQuery();
            Vector vec = new Vector();
            while (rs.next()) {
                vec.add(rs.getString(1));
            }
            return vec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public void loadVacCombobox(JComboBox cbDep, JComboBox cbDes, JComboBox cbSec) {
        Vector depNos = vacLoadDepNo();
        Vector desIds = vacLoadDesId();
        Vector secIds = vacLoadSecId();

        cbDep.removeAllItems();
        cbDes.removeAllItems();
        cbSec.removeAllItems();

        cbDep.addItem("Please select...");
        cbDes.addItem("Please select...");
        cbSec.addItem("Please select...");

        for (int i = 0; i < depNos.size(); i++) {
            cbDep.addItem(depNos.get(i));            
        }

        for (int i = 0; i < desIds.size(); i++) {
            cbDes.addItem(desIds.get(i));
        }

        for (int i = 0; i < secIds.size(); i++) {
            cbSec.addItem(secIds.get(i));
        }
    }

    public boolean insertVac(Vector VacData) {
        try {
            Vector vecVacData = VacData;
            rsVac.moveToInsertRow();
            for (int i = 0; i < vecVacData.size(); i++) {
                if (i == 1) {    // DepNo
                    rsVac.updateInt(2, Integer.parseInt(vecVacData.get(1).toString()));
                    continue;
                }
                if (i == 4) { //NoofVacancy
                    rsVac.updateInt(5, Integer.parseInt(vecVacData.get(4).toString()));
                    continue;
                }

                rsVac.updateString(i + 1, vecVacData.get(i).toString());
            }
            rsVac.insertRow();
            loadDataVac();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertVac: " + e.getMessage());
            return false;
        }
    }

    public boolean updatateVac(Vector vecVacData) {
        try {
            rsVac.beforeFirst();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").equals(vecVacData.get(0))) {
                    for (int i = 0; i < vecVacData.size(); i++) {
                        if (i == 1) {    // DepNo
                            rsVac.updateInt(2, Integer.parseInt(vecVacData.get(1).toString()));
                            continue;
                        }
                        if (i == 4) { //NoofVacancy
                            rsVac.updateInt(5, Integer.parseInt(vecVacData.get(4).toString()));
                            continue;
                        }
                        rsVac.updateString(i + 1, vecVacData.get(i).toString());
                    }
                    rsVac.updateRow();
                    loadDataVac();
                    return true;
                }
            }

            return false;
        } catch (Exception e) {
            System.out.println("Error in updateEmp: " + e.getMessage());
            return false;
        }
    }

    public boolean deleteVac(String VacId) {
        try {
            rsVac.beforeFirst();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").trim().equals(VacId)) {
                    rsVac.deleteRow();
                    loadDataVac();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Have data in orther table");
            return false;
        }
    }

    public DefaultTableModel loadTableVac() {
        try {
            String query = "Select * From Vacancies";
            Statement ps = conn.createStatement();
            ResultSet rs = ps.executeQuery(query);

            Vector nameCol = new Vector();
            nameCol.add("Vacancy ID");
            nameCol.add("Department No");
            nameCol.add("Section ID");
            nameCol.add("Designation ID");


            DefaultTableModel dtm = new DefaultTableModel(nameCol, 0);

            while (rs.next()) {

                Vector dataRow = new Vector();
                dataRow.add(rs.getString("VACANCYID"));
                dataRow.add(rs.getString("DEPTNO"));
                dataRow.add(rs.getString("SECTID"));
                dataRow.add(rs.getString("DESIGNID"));
                dtm.addRow(dataRow);
            }
            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
